using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace WindowsApplication1
{
    public class Db
    {
        public static string GetDbPath()
        {
            string rootPath = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
            rootPath = rootPath.Substring(8, rootPath.LastIndexOf("/") - 37);
            rootPath = rootPath + "Report0602.mdb";
            string connStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + rootPath;
            return connStr;
        }
        
        public static int Query(string sql)
        {
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.CommandText = sql;
            return ExecuteNonQuery(objCommand);
        }

        public static DataTable GetList(string sql)
        {
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.CommandText = sql;
            return ExecuteDataTable(objCommand);
        }

        public static object GetOne(string sql)
        {
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.CommandText = sql;
            return ExecuteScalar(objCommand);
        }

        public static int ExecuteNonQuery(OleDbCommand objCommand)
        {
            OleDbConnection Conn = new OleDbConnection(GetDbPath());
            objCommand.Connection = Conn;
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                    Conn.Open();
                return objCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                objCommand.Dispose();
                if (Conn.State == System.Data.ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
        }

        public static DataTable ExecuteDataTable(OleDbCommand objCommand)
        {
            OleDbConnection Conn = new OleDbConnection(GetDbPath());
            objCommand.Connection = Conn;
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(objCommand);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                objCommand.Dispose();
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
            }
        }


        public static object ExecuteScalar(OleDbCommand objCommand)
        {
            OleDbConnection Conn = new OleDbConnection(GetDbPath());
            objCommand.Connection = Conn;
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                    Conn.Open();
                return objCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                objCommand.Dispose();
                if (Conn.State == System.Data.ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
        }
    }
}
